/// ****************************************************************************
/// Author:	        Robin Zhu
/// Create Date:	2011-11-1
/// Purpose: 		SQL语句构造类文件
/// ****************************************************************************
/// Modify By		Date			Remark
/// Robin           2012-3-27       为方法编码的调用增加调用者类型参数
/// Robin           2012-4-3        修正对Count, Max, Min的支持
/// Robin           2012-6-14       当使用Distinct时，不生成Order语句
/// Robin           2012-6-14       支持非匿名类的初始化表作为Select的表达式
/// Robin           2012-6-14       修复处理join时出现的问题
/// Robin           2012-6-14       增加Skip方法的支持
/// Robin           2012-7-25       增加对Group By的支持
/// Robin           2012-7-25       增加对Except, Concat(Union All), Union, Intersect的支持
/// Robin           2012-10-18      修正使用聚合函数时，同时有Order语句的错误
/// Robin           2012-11-10      修改BuildUpdateFields，增加对对象常量的支持
/// Robin           2013-4-30       使用缓存的访问器Delegate读取属性值
/// Robin           2013-5-30       修正对Count和直接初始化与数据表不相关的类时，字段名不一致问题
/// Robin           2013-5-31       修正直接初始化的与数据库不对应的类时生成SQL语句的Bug
/// Robin           2013-6-6        修正在对Skip编码时直接初始化的与数据库不对应的类时生成SQL语句的Bug
/// Robin           2013-6-7        修正在对Skip编码时直接初始化的与数据库不对应的类时生成SQL语句的Bug
/// Robin           2013-7-17       支持Last和LastOrDefault
/// Robin           2013-7-23       调用SqlEncoder的GetSubQuery方法，而不是直接生成
/// Robin           2014-2-17       支持多个from的写法
/// Robin           2015-3-17       修复Distinct编码时生成的查询字段列表不正确的问题
/// Robin           2015-12-1       使更新布尔值字段时使生成正确的语句
/// ****************************************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Linq.Expressions;
using System.Reflection;
using RB.DataAccess.Attributes;
using RB.DataAccess.Linq;
using System.Diagnostics;
using System.Collections;

namespace RB.DataAccess
{
    /// <summary>
    /// SQL语句构造类，将表达式翻译成SQL语句
    /// </summary>
    public class SqlBuilder
    {
        private SqlEncoder _encoder = null;

        public SqlBuilder(SqlEncoder encoder)
        {
            _encoder = encoder;
        }

        public string BuildCondition(LambdaExpression predict)
        {
            StringBuilder strb = new StringBuilder();
            BuildCondition(strb, predict.Parameters, predict.Body);

            return strb.ToString();
        }

        protected bool BuildCondition(StringBuilder strb, IEnumerable<ParameterExpression> parameters,
            Expression expression)
        {
            return BuildCondition(strb, parameters, expression, true);
        }

        protected bool BuildCondition(StringBuilder strb, IEnumerable<ParameterExpression> parameters,
            Expression expression, bool isCondition)
        {
            bool isNullValue = false;
            bool foundWay = false;
            if (expression is UnaryExpression)
            {

                isNullValue = BuildUnaryExpression(strb, parameters, (UnaryExpression)expression, isCondition);
                foundWay = true;
            }
            else if (expression is BinaryExpression)
            {
                BuildBinaryExpression(strb, parameters, (BinaryExpression)expression, isCondition);
                foundWay = true;
            }
            else if (expression is MemberExpression)
            {
                foundWay = BuildMemberExpression(strb, parameters, (MemberExpression)expression, isCondition);
            }
            else if (expression is MethodCallExpression)
            {
                try
                {
                    BuildMethodCall(strb, parameters, (MethodCallExpression)expression);
                    foundWay = true;
                }
                catch
                {
                    // 如果找不到函数，则尝试计算函数的值
                }
            }
            else if (expression is ParameterExpression)
            {
                // 如果是参数，则取字段表
                BuildSelectFields(strb, parameters, (ParameterExpression)expression);
                foundWay = true;
            }
            else if (expression is ConstantExpression)
            {
                var constExpr = (ConstantExpression)expression;
                if (constExpr.Value is ISqlQueryable)
                {
                    BuildSelectFields(strb, (ISqlQueryable)constExpr.Value);
                    foundWay = true;
                }
            }

            if (!foundWay)
            {
                if (isCondition)
                {
                    bool val = false;
                    TryExtractValue(expression, ref val);
                    strb.Append(_encoder.GetBooleanExpression(val));
                }
                else
                {
                    object val = null;
                    TryExtractValue(expression, ref val);
                    strb.Append(_encoder.GetConstant(val));

                    isNullValue = (val == null);
                }
            }
            return isNullValue;
        }

        private bool BuildMemberExpression(StringBuilder strb, IEnumerable<ParameterExpression> parameters, 
            MemberExpression expression, bool isCondition)
        {
            bool result = false;
            Expression exprNext = expression;
            while (exprNext is MemberExpression)
            {
                MemberExpression memberExpr = (MemberExpression)exprNext;

                if (memberExpr.Expression != null &&
                    memberExpr.Expression.NodeType == ExpressionType.Parameter)
                {
                    result = true;
                    break;
                }
                exprNext = memberExpr.Expression;
            }
            if (result)
            {
                string fieldName = ExtractFieldName(expression, parameters);
                if (isCondition)
                {
                    strb.Append(_encoder.GetBooleanExpression(fieldName));
                }
                else
                {
                    strb.Append(fieldName);
                }
            }
            return result;
        }

        protected string ExtractFieldName(MemberExpression memberExpr, IEnumerable<ParameterExpression> parameters)
        {
            string alias = null;
            if (parameters != null && parameters.Count() > 1)
            {
                if (memberExpr.Expression is ParameterExpression)
                {
                    if (parameters.Any(p => p.Type == memberExpr.Expression.Type))
                        alias = ((ParameterExpression)memberExpr.Expression).Name;
                }
                else if (memberExpr.Expression is MemberExpression)
                    alias = ((MemberExpression)memberExpr.Expression).Member.Name;
            }
            // 获取使用FieldNameAttribute定义的字段名
            var memberInfo = memberExpr.Member;
            string fieldName = memberInfo.Name;
            var fieldNameAttrs = memberInfo.GetCustomAttributes(typeof(FieldNameAttribute), true);
            if (fieldNameAttrs != null && fieldNameAttrs.Length > 0)
            {
                fieldName = ((FieldNameAttribute)fieldNameAttrs[0]).Value;
            }
            return _encoder.GetFieldName(fieldName, alias);
        }

        protected bool TryExtractValue<TValue>(Expression expression, ref TValue val)
        {
            bool result = false;
            ConstantExpression constExpr = expression as ConstantExpression;
            if (constExpr != null)
            {
                val = (TValue)constExpr.Value;
                result = true;
            }
            else
            {
                try
                {
                    if (typeof(TValue) == expression.Type)
                    {
                        var Lambda = Expression.Lambda<Func<TValue>>(expression);
                        var func = Lambda.Compile();
                        val = func();
                    }
                    else
                    {
                        var lambda = Expression.Lambda(expression);
                        var func = lambda.Compile();
                        val = (TValue)func.DynamicInvoke();
                    }
                    result = true;
                }
                catch (ArgumentException)
                {
                }
            }
            return result;
        }


        private void BuildMethodCall(StringBuilder strb, IEnumerable<ParameterExpression> parameters, MethodCallExpression expression)
        {
            StringBuilder strParamBuilder = new StringBuilder();
            string caller = null;
            Type callerType = null;
            if (expression.Object != null)
            {
                BuildCondition(strParamBuilder, parameters, expression.Object, false);
                caller = strParamBuilder.ToString();
                callerType = expression.Object.Type;
            }
            string[] arguments = new string[expression.Arguments.Count];
            int i = 0;
            foreach (var argumentExpr in expression.Arguments)
            {
                strParamBuilder.Clear();
                BuildCondition(strParamBuilder, parameters, argumentExpr, false);
                arguments[i++] = strParamBuilder.ToString();
            }
            strb.Append(_encoder.GetMethodCall(expression.Method.Name, caller, arguments, callerType));
        }

        private void BuildBinaryExpression(StringBuilder strb, IEnumerable<ParameterExpression> parameters,
            BinaryExpression binExpr)
        {
            BuildBinaryExpression(strb, parameters, binExpr, false);
        }

        private void BuildBinaryExpression(StringBuilder strb, IEnumerable<ParameterExpression> parameters,
            BinaryExpression binExpr, bool isCondition)
        {
            bool isMethodCall = false;
            bool isConditionNext = (binExpr.NodeType == ExpressionType.AndAlso ||
                    binExpr.NodeType == ExpressionType.OrElse);

            bool isNullValue = false;
            StringBuilder strBinaryBuilder1 = new StringBuilder();
            isNullValue |= BuildCondition(strBinaryBuilder1, parameters, binExpr.Left, isConditionNext);
            StringBuilder strBinaryBuilder2 = new StringBuilder();
            isNullValue |= BuildCondition(strBinaryBuilder2, parameters, binExpr.Right, isConditionNext);

            string op = _encoder.GetOperator(binExpr.Type, binExpr.NodeType, isNullValue, out isMethodCall);
            if (isMethodCall)
            {
                StringBuilder strBinaryBuilder = new StringBuilder();
                strBinaryBuilder.Append(op);
                strBinaryBuilder.Append("(");
                strBinaryBuilder.Append(strBinaryBuilder1.ToString());
                strBinaryBuilder.Append(", ");
                strBinaryBuilder.Append(strBinaryBuilder2.ToString());
                strBinaryBuilder.Append(") ");

                if (isCondition)
                    strb.Append(_encoder.GetBooleanExpression(strBinaryBuilder.ToString()));
                else
                    strb.Append(strBinaryBuilder.ToString());
            }
            else
            {

                strb.Append("(");
                strb.Append(strBinaryBuilder1.ToString());
                strb.Append(" ");
                strb.Append(op);
                strb.Append(" ");
                strb.Append(strBinaryBuilder2.ToString());
                strb.Append(") ");
            }
        }

        private bool BuildUnaryExpression(StringBuilder strb, IEnumerable<ParameterExpression> parameters, UnaryExpression unrayExpr, bool isCondition)
        {
            bool isNullValue = false;
            if (unrayExpr.NodeType == ExpressionType.Convert || unrayExpr.NodeType == ExpressionType.ConvertChecked)
            {
                isNullValue = BuildCondition(strb, parameters, unrayExpr.Operand, isCondition);
            }
            else if (unrayExpr.NodeType == ExpressionType.Quote)
            {
                isNullValue = BuildCondition(strb, parameters, ((LambdaExpression)unrayExpr.Operand).Body, isCondition);
            }
            else
            {
                bool isMethodCall = false;
                strb.Append(_encoder.GetOperator(unrayExpr.Type, unrayExpr.NodeType, false, out isMethodCall));
                strb.Append(" ");
                BuildCondition(strb, parameters, unrayExpr.Operand, (unrayExpr.NodeType == ExpressionType.Not) || isCondition);
                strb.Append(" ");
            }
            return isNullValue;
        }

        private object BuildFieldValueUnaryExpression(StringBuilder strb, IEnumerable<ParameterExpression> parameters, UnaryExpression unrayExpr)
        {
            object val = null;
            if (unrayExpr.NodeType == ExpressionType.Convert || unrayExpr.NodeType == ExpressionType.ConvertChecked)
            {
                val = BuildFieldValue(strb, parameters, unrayExpr.Operand);
            }
            else if (unrayExpr.NodeType == ExpressionType.Quote)
            {
                val = BuildFieldValue(strb, parameters, ((LambdaExpression)unrayExpr.Operand).Body);
            }
            else
            {
                bool isMethodCall = false;
                Type type = unrayExpr.Type;
                if (unrayExpr.NodeType == ExpressionType.Not && type == typeof(bool))
                {
                    type = typeof(int);
                }

                strb.Append(_encoder.GetOperator(type, unrayExpr.NodeType, false, out isMethodCall));
                strb.Append(" ");
                BuildFieldValue(strb, parameters, unrayExpr.Operand);
                                
                strb.Append(" ");
            }
            return val;
        }

        public string BuildSelectCommand(Expression expression)
        {
            IList<ParameterExpression> parameters = new List<ParameterExpression>();

            return "SELECT " + BuildSelectClause(expression, parameters);
        }

        private string BuildQueryClause(Expression expression, IList<ParameterExpression> parameters, bool bSubQuery)
        {
            return BuildQueryClause(expression, parameters, null, bSubQuery);
        }

        private string BuildQueryClause(Expression expression, IList<ParameterExpression> parameters, ParameterExpression queryParam, bool bSubQuery)
        {
            if (expression is MethodCallExpression)
            {
                MethodCallExpression methodExpr = (MethodCallExpression)expression;
                string methodName = methodExpr.Method.Name;

                if (methodName == "Select" || methodName == "Where" || methodName.StartsWith("OrderBy") || methodName == "GroupBy" ||
                    methodName == "Join" && bSubQuery)
                {
                    string selectClause = BuildSelectClause(methodExpr, parameters);
                    string alias = null;
                    if (queryParam != null)
                    {
                        alias = queryParam.Name;
                        parameters.Add(queryParam);
                    }
                    selectClause = _encoder.GetSubQuery(selectClause, alias);
                    return selectClause;
                }
                else if (methodName == "Join")
                {
                    return BuildJoinClause(methodExpr, parameters);
                }
                else if (methodName == "SelectMany")
                {
                    return BuildSelectManyClause(methodExpr, parameters);
                }
                else if (methodName == "Query" && methodExpr.Object.Type.IsAssignableFrom(typeof(Executor)))
                {
                    ISqlQueryable query = (ISqlQueryable)MethodCallExpression.Lambda(methodExpr).Compile().DynamicInvoke();
                    return BuildQueryName(query, parameters, queryParam);
                }
                else if (methodName == "Except" || methodName == "Concat" || methodName == "Union" || methodName == "Intersect")
                {
                    return "(" + GetSetOperationStatement(methodExpr) + ") AS SET_OPERATION_RESULT_" + (new Random()).Next().ToString();
                }
                else
                {
                    var query = Expression.Lambda(expression).Compile().DynamicInvoke() as ISqlQueryable;
                    if (query != null)
                        return BuildQueryName(query, parameters, queryParam);
                }
            }
            else
            {
                ConstantExpression constExpr = expression as ConstantExpression;
                if (constExpr != null)
                {
                    if (constExpr.Value is ISqlQueryable)
                    {
                        return BuildQueryName((ISqlQueryable)constExpr.Value, parameters, queryParam);
                    }
                }
            }
            throw new ArgumentException("Unknown Expression.");
        }

        /// <summary>
        /// 生成集合操作语句
        /// </summary>
        /// <param name="methodExpr">集合操作运算符</param>
        /// <returns></returns>
        private string GetSetOperationStatement(MethodCallExpression methodExpr)
        {
            string query1 = BuildSelectCommand(methodExpr.Arguments[0]);
            string op = _encoder.GetMethodCall(methodExpr.Method.Name, null, null, null);
            string query2 = BuildSelectCommand(methodExpr.Arguments[1]);
            return query1 + " " + op + " " + query2;
        }

        private string BuildOrderClause(MethodCallExpression methodExpr, IList<ParameterExpression> parameters, bool firstOrderClause)
        {
            StringBuilder strb = new StringBuilder();
            string queryClause = BuildQueryClause(methodExpr.Arguments[0], parameters, false);
            strb.Append(queryClause);
            if (firstOrderClause)
                strb.Append(" ORDER BY ");
            else
                strb.Append(",");
            BuildCondition(strb, parameters, methodExpr.Arguments[1], false);
            if (methodExpr.Method.Name.EndsWith("Descending"))
            {
                strb.Append(" DESC");
            }
            return strb.ToString();
        }

        private string BuildQueryName(ISqlQueryable query, IList<ParameterExpression> parameters)
        {
            return BuildQueryName(query, parameters, null);
        }

        private string BuildQueryName(ISqlQueryable query, IList<ParameterExpression> parameters, ParameterExpression queryParam)
        {
            string queryName = query.QueryName;
            string alias = null;
            if (queryParam != null)
            {
                alias = queryParam.Name;
                parameters.Add(queryParam);
            }
            return _encoder.GetTableName(queryName, alias);
        }

        private string BuildSelectManyClause(MethodCallExpression methodExpr, IList<ParameterExpression> parameters)
        {
            StringBuilder strb = new StringBuilder();
            LambdaExpression lambda = (LambdaExpression)((UnaryExpression)methodExpr.Arguments[2]).Operand;
            var queryParams = lambda.Parameters;
            Expression left = methodExpr.Arguments[0];
            Expression right = methodExpr.Arguments[1];
            strb.Append(BuildQueryClause(left, parameters, queryParams[0], false));
            strb.Append(" CROSS JOIN ");
            strb.Append(BuildQueryClause(((LambdaExpression)(((UnaryExpression)right).Operand)).Body, parameters, queryParams[1], false));
            return strb.ToString();
        }

        private string BuildJoinClause(MethodCallExpression methodExpr, IList<ParameterExpression> parameters)
        {
            StringBuilder strb = new StringBuilder();
            LambdaExpression lambda = (LambdaExpression)((UnaryExpression)methodExpr.Arguments[4]).Operand;
            var queryParams = lambda.Parameters;

            // 使用DefaultIfEmpty生成OUT JOIN
            string joinWord = " INNER JOIN ";
            Expression right = methodExpr.Arguments[1];
            MethodCallExpression rightMethod = right as MethodCallExpression;
            if (rightMethod != null && rightMethod.Method.Name == "DefaultIfEmpty")
            {
                right = rightMethod.Arguments[0];
                joinWord = " LEFT OUTER JOIN ";
            }
            Expression left = methodExpr.Arguments[0];

            MethodCallExpression leftMethod = left as MethodCallExpression;
            if (leftMethod != null && leftMethod.Method.Name == "DefaultIfEmpty")
            {
                left = leftMethod.Arguments[0];
                if (joinWord == " INNER JOIN ")
                {
                    joinWord = " RIGHT OUTER JOIN ";
                }
                else
                {
                    joinWord = " FULL OUTER JOIN ";
                }
            }
            
            strb.Append(BuildQueryClause(left, parameters, queryParams[0], false));
            strb.Append(joinWord);
            strb.Append(BuildQueryClause(right, parameters, queryParams[1], false));
            strb.Append(" ON ");
            LambdaExpression field1 = (LambdaExpression)((UnaryExpression)methodExpr.Arguments[2]).Operand;
            BuildCondition(strb, parameters, field1.Body, false);
            strb.Append("=");
            LambdaExpression field2 = (LambdaExpression)((UnaryExpression)methodExpr.Arguments[3]).Operand;
            BuildCondition(strb, parameters, field2.Body, false);
            return strb.ToString();
        }

        private string BuildSelectClause(Expression expression, IList<ParameterExpression> parameters)
        {
            return BuildSelectClause(expression, parameters, true);
        }

        private string BuildSelectClause(Expression expression, IList<ParameterExpression> parameters, bool buildField)
        {
            StringBuilder strb = new StringBuilder();

            Expression queryExpr = expression;
            Expression fieldExpr = null;
            Stack<Expression> whereExprs = new Stack<Expression>();
            Stack<Tuple<Expression, bool>> orderExprs = new Stack<Tuple<Expression, bool>>();

            int takeCount = 0;
            int skipCount = 0;
            string aggregateFunction = null;
            bool findSelect = false;
            bool groupBy = false;
            string groupByKey = null;
            Expression groupByKeyExpr = null;
            Stack<Expression> havingExpr = null;
            bool bWhereFirst = false;
            bool bSubQuery = false;
            bool bReverseOrder = false;

            while (expression is MethodCallExpression)
            {
                MethodCallExpression methodExpr = (MethodCallExpression)expression;

                string methodName = methodExpr.Method.Name;

                if (methodName == "Where")
                {
                    whereExprs.Push(methodExpr.Arguments[1]);
                    if (!findSelect)
                        bWhereFirst = true;
                }
                else if (methodName == "Select")
                {
                    if (!findSelect)
                    {
                        fieldExpr = methodExpr.Arguments[1];
                        findSelect = true;
                        bWhereFirst = false;
                    }
                    else
                    {
                        break;
                    }
                }
                else if (methodName == "Join")
                {
                    if (!findSelect)
                    {
                        fieldExpr = methodExpr.Arguments[4];
                    }
                    bSubQuery = bWhereFirst;
                    break;
                }
                else if (methodName == "First" || methodName == "FirstOrDefault" ||
                    methodName == "Single" || methodName == "SingleOrDefault")
                {
                    takeCount = 1;
                }
                else if (methodName == "Last" || methodName == "LastOrDefault")
                {
                    takeCount = 1;
                    bReverseOrder = true;
                }
                else if (methodName == "Take")
                {
                    TryExtractValue(methodExpr.Arguments[1], ref takeCount);
                }
                else if (methodName == "Skip")
                {
                    TryExtractValue(methodExpr.Arguments[1], ref skipCount);
                    bWhereFirst = true;
                }
                else if (methodName == "SelectMany")
                {
                    if (!findSelect)
                    {
                        fieldExpr = methodExpr.Arguments[2];
                    }
                    else
                    {
                        break;
                    }
                }
                else if (methodName.StartsWith("OrderBy") || methodName.StartsWith("ThenBy"))
                {
                    bool desc = methodName.EndsWith("Descending");
                    if (bReverseOrder)
                        desc = !desc;
                    orderExprs.Push(new Tuple<Expression, bool>(methodExpr.Arguments[1], desc));
                }
                else if (methodName == "GroupBy")
                {
                    groupBy = true;
                    groupByKeyExpr = methodExpr.Arguments[1];
                    havingExpr = new Stack<Expression>();
                    while (whereExprs.Count > 0)
                    {
                        havingExpr.Push(whereExprs.Pop());
                    }
                }
                else if (methodName == "Except" || methodName == "Concat" || methodName == "Union" || methodName == "Intersect")
                {
                    break;
                }
                else
                {
                    aggregateFunction = methodName;
                    if (methodExpr.Arguments.Count > 1)
                    {
                        if (methodName == "Count")
                        {
                            whereExprs.Push(methodExpr.Arguments[1]);
                        }
                        else
                        {
                            fieldExpr = methodExpr.Arguments[1];
                        }
                    }
                }
                queryExpr = methodExpr.Arguments[0];
                expression = queryExpr;
            }

            string queryClause = BuildQueryClause(queryExpr, parameters, bWhereFirst);

            if (buildField)
            {
                // 须排除方法调用但fieldExpr为空的情况
                if (fieldExpr == null && queryExpr.NodeType != ExpressionType.Call)
                    fieldExpr = queryExpr;
                BuildSelectFields(strb, parameters, fieldExpr, aggregateFunction, bSubQuery);
            }
            strb.Append(" FROM ");
            strb.Append(queryClause);

            // 生成Where语句
            BuildWhereClause(strb, parameters, whereExprs);

            if (groupBy)
            {
                StringBuilder strbKey = new StringBuilder();
                BuildCondition(strbKey, parameters, groupByKeyExpr, false);
                groupByKey = strbKey.ToString();
                strb.Append(" GROUP BY ");
                strb.Append(groupByKey);

                BuildWhereClause(strb, parameters, havingExpr, true);
            }

            // 生成Order By语句
            // 当使用distinct时，不做排序
            string orderClause = null;
            if (aggregateFunction == null)
            {
                // 如果使用Last或LastOrDefault，则必须有排序子句
                if (bReverseOrder && orderExprs.Count == 0)
                    throw new Exception("At least one order clause is required when Last or LastOrDefault is used.");
                orderClause = BuildOrderClause(parameters, orderExprs);
                if (!string.IsNullOrEmpty(orderClause))
                {
                    strb.Append(" ");
                    strb.Append(orderClause);
                }
            }

            // 跳过前多少条记录
            if (skipCount > 0)
            {
                BuildSkipClause(strb, parameters, orderClause, skipCount);
            }

            if (takeCount > 0)
            {
                return _encoder.GetTopN(takeCount, strb.ToString());
            }
            if (groupBy)
            {
                strb.Replace("[Key]", groupByKey);
            }
            return strb.ToString();
        }

        private void BuildSkipClause(StringBuilder strb, IList<ParameterExpression> parameters, string orderClause, int skipCount)
        {
            StringBuilder strOrderBuilder = new StringBuilder();

            string beforeSkip = strb.ToString();
            string afterSkip = _encoder.SkipRecords(skipCount, beforeSkip, orderClause);
            strb.Clear();
            strb.Append(afterSkip);
        }

        private string BuildOrderClause(IList<ParameterExpression> parameters, Stack<Tuple<Expression, bool>> orderExprs)
        {
            string delimiter = "ORDER BY ";
            StringBuilder strOrderBuilder = new StringBuilder();
            
            while (orderExprs.Count > 0)
            {
                strOrderBuilder.Append(delimiter);
                delimiter = ",";
                Tuple<Expression, bool> orderExpr = orderExprs.Pop();
                BuildCondition(strOrderBuilder, parameters, orderExpr.Item1, false);
                if (orderExpr.Item2)
                {
                    strOrderBuilder.Append(" DESC");
                }
            }
            return strOrderBuilder.ToString();
        }

        private void BuildWhereClause(StringBuilder strb, IList<ParameterExpression> parameters, Stack<Expression> whereExprs)
        {
            BuildWhereClause(strb, parameters, whereExprs, false);
        }

        private void BuildWhereClause(StringBuilder strb, IList<ParameterExpression> parameters, Stack<Expression> whereExprs, bool groupBy)
        {
            string delimiter = " WHERE ";
            if (groupBy)
                delimiter = " HAVING ";
            while (whereExprs.Count > 0)
            {
                strb.Append(delimiter);
                delimiter = " AND ";

                Expression whereExpr = whereExprs.Pop();
                BuildCondition(strb, parameters, whereExpr);
            }
        }

        private string BuildWhereClause(MethodCallExpression methodExpr, IList<ParameterExpression> parameters)
        {
            StringBuilder strb = new StringBuilder();
            string queryClause = BuildQueryClause(methodExpr.Arguments[0], parameters, false);
            strb.Append(queryClause);
            strb.Append(" WHERE ");
            BuildCondition(strb, parameters, methodExpr.Arguments[1]);
            return strb.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strb"></param>
        /// <param name="parameters"></param>
        /// <param name="expression"></param>
        /// <param name="aggregateFunction"></param>
        /// <returns>是否Select列表中使用了新的类型（可能带有重命名字段）</returns>
        private bool BuildSelectFields(StringBuilder strb, IEnumerable<ParameterExpression> parameters,
            Expression expression, string aggregateFunction, bool bSubQuery)
        {
            bool bNewType = false;

            while (expression != null && (expression.NodeType == ExpressionType.Quote || expression.NodeType == ExpressionType.Convert))
            {
                if (expression.NodeType == ExpressionType.Quote)
                {
                    expression = ((LambdaExpression)((UnaryExpression)expression).Operand).Body;
                }
                else if (expression.NodeType == ExpressionType.Convert)
                {
                    expression = ((UnaryExpression)expression).Operand;
                }
            }
            ParameterExpression paramExpr = null;
            if (expression != null && expression.NodeType == ExpressionType.MemberAccess)
            {
                paramExpr = FindParameterByType(parameters, expression.Type);
            }
            if (aggregateFunction != null)
            {
                strb.Append(aggregateFunction);
                
                if (aggregateFunction == "Distinct")
                {
                    strb.Append(" ");
                    if (paramExpr != null)
                    {
                        BuildSelectFields(strb, parameters, paramExpr);
                    }
                    else
                    {
                        BuildCondition(strb, parameters, expression, false);
                    }
                }
                else
                {
                    strb.Append("(");
                    if (expression != null && (expression.Type == typeof(string) || !expression.Type.IsClass))
                    {
                        BuildCondition(strb, parameters, expression, false);
                        //BuildMemberExpression(strb, parameters, (MemberExpression)expression, false);
                    }
                    else if (aggregateFunction == "Count")
                    {
                        strb.Append("*");
                    }
                    strb.Append(")");
                }
                
            }
            else
            {
                if (expression == null)
                {
                    strb.Append("*");
                }
                else
                {
                    switch (expression.NodeType)
                    {
                        case ExpressionType.Constant:
                            {
                                ConstantExpression constExpr = expression as ConstantExpression;
                                ISqlQueryable query = constExpr.Value as ISqlQueryable;
                                if (query != null)
                                {
                                    BuildSelectFields(strb, query);
                                }
                            }
                            break;
                        case ExpressionType.Parameter:
                            BuildSelectFields(strb, parameters, (ParameterExpression)expression);
                            break;
                        case ExpressionType.MemberAccess:
                            if (paramExpr == null)
                            {
                                BuildMemberExpression(strb, parameters, (MemberExpression)expression, false);
                            }
                            else
                            {
                                BuildSelectFields(strb, parameters, paramExpr);
                            }
                            break;
                        case ExpressionType.New:
                            BuildSelectFields(strb, parameters, (NewExpression)expression);
                            break;
                        case ExpressionType.MemberInit:
                            bNewType = BuildSelectFields(strb, parameters, (MemberInitExpression)expression, bSubQuery);
                            break;
                        case ExpressionType.Call:
                            BuildMethodCall(strb, parameters, (MethodCallExpression)expression);
                            break;
                    }
                }
            }
            return bNewType;
        }

        
        private ParameterExpression FindParameterByType(IEnumerable<ParameterExpression> parameters, Type type)
        {
            foreach (var param in parameters)
            {
                if (param.Type == type)
                {
                    return param;
                }
            }
            return null;
        }

        private bool BuildSelectFields(StringBuilder strb, IEnumerable<ParameterExpression> parameters, MemberInitExpression memberInitExpression, bool bSubQuery)
        {
            bool bNewType = false;
            foreach (var binding in memberInitExpression.Bindings)
            {
                if (binding.BindingType == MemberBindingType.Assignment)
                {
                    MemberAssignment assignBinding = (MemberAssignment)binding;
                    if (!bSubQuery)
                    {
                        BuildCondition(strb, parameters, assignBinding.Expression, false);
                        strb.Append(" AS ");
                    }
                    strb.Append(binding.Member.Name);
                    strb.Append(",");

                    //Expression exp = assignBinding.Expression;
                    //while (exp is UnaryExpression)
                    //{
                    //    exp = ((UnaryExpression)exp).Operand;
                    //}
                    //if (!(exp is MemberExpression))
                    //{
                    //    bNewType = true;
                    //}
                    //else if (((MemberExpression)exp).Member.Name != binding.Member.Name)
                    //{
                    //    bNewType = true;
                    //}
                }
            }
            strb.Length--;
            return bNewType;
        }

        private void BuildSelectFields(StringBuilder strb, IEnumerable<ParameterExpression> parameters, NewExpression newExpr)
        {
            for (int i = 0; i < newExpr.Arguments.Count; i++)
            {
                BuildCondition(strb, parameters, newExpr.Arguments[i], false);
                strb.Append(",");
            }
            strb.Length--;
        }

        private void BuildSelectFields(StringBuilder strb, ISqlQueryable iSqlQueryable)
        {
            var maps = iSqlQueryable.QueryPropertyFieldMap;
            foreach (var fieldName in maps.Values)
            {
                strb.Append(_encoder.GetFieldName(fieldName, null));
                strb.Append(",");
            }
            strb.Length--;
        }

        private void BuildSelectFields(StringBuilder strb, IEnumerable<ParameterExpression> parameters, ParameterExpression paramExpr)
        {
            Type mapperType = typeof(TypeMapper<>).MakeGenericType(paramExpr.Type);
            var propInfo = mapperType.GetProperty("QueryPropertyFieldMap");//, BindingFlags.Static | BindingFlags.GetProperty);
            var maps = (Dictionary<string, string>)propInfo.GetValue(null, null);
            
            string alias = null;
            if (parameters.Count() > 0 && parameters.Any(p => p.Type == paramExpr.Type))
                alias = paramExpr.Name;
            foreach (var fieldName in maps.Values)
            {
                strb.Append(_encoder.GetFieldName(fieldName, alias));
                strb.Append(",");
            }
            strb.Length--;
        }

        public string BuildDeleteCommand<T>(System.Linq.Expressions.Expression<Func<T, bool>> predict)
        {
            return BuildDeleteCommand(predict, null);
        }

        public string BuildDeleteCommand<T>(System.Linq.Expressions.Expression<Func<T, bool>> predict, string mappingTo)
        {
            // 拼接插入字符串
            StringBuilder strb = new StringBuilder();
            strb.Append("DELETE FROM ");
            strb.Append(GetTableName<T>(mappingTo));

            if (predict != null)
            {
                strb.Append(" WHERE ");
                string condition = BuildCondition(predict);
                strb.Append(condition);
            }
            return strb.ToString();
        }

        public string BuildDeleteCommand<T>(IQueryable<T> query)
        {
            StringBuilder strb = new StringBuilder();
            strb.Append("DELETE ");
            IList<ParameterExpression> parameters = new List<ParameterExpression>();
            string queryClause = BuildSelectClause(query.Expression, parameters, false);
            if (parameters.Count > 1)
            {
                var paramExpr = FindParameterByType(parameters, typeof(T));
                if (paramExpr != null)
                {
                    strb.Append(paramExpr.Name);
                }
            }
            strb.Append(queryClause);
            return strb.ToString();
        }

        private string GetTableName<T>(string mappingTo)
        {
            return mappingTo ?? TypeMapper<T>.TableName;
        }

        public string BuildUpdateCommand<T, TFields>(Expression<Func<T, TFields>> fields, Expression<Func<T, bool>> predict)
        {
            return BuildUpdateCommand(fields, predict, null);
        }

        public string BuildUpdateCommand<T, TFields>(Expression<Func<T, TFields>> fields, Expression<Func<T, bool>> predict, string mappingTo)
        {
            if (fields == null)
            {
                throw new ArgumentNullException("fields", "更新字段列表不能为空");
            }

            StringBuilder strb = new StringBuilder();
            strb.Append("UPDATE ");
            strb.Append(GetTableName<T>(mappingTo));
            strb.Append(" SET ");

            BuildUpdateFields(strb, fields);

            if (predict != null)
            {
                strb.Append(" WHERE ");
                string condition = BuildCondition(predict);
                strb.Append(condition);
            }
            return strb.ToString();
        }

        public string BuildUpdateCommand<T, TFields>(Expression<Func<T, TFields>> fields, IQueryable<T> query)
        {
            if (fields == null)
            {
                throw new ArgumentNullException("fields", "更新字段列表不能为空");
            }

            StringBuilder strb = new StringBuilder();
            strb.Append("UPDATE ");
            IList<ParameterExpression> parameters = new List<ParameterExpression>();
            string queryClause = BuildSelectClause(query.Expression, parameters, false);
            bool bTableFound = false;
            if (parameters.Count > 1)
            {
                var paramExpr = FindParameterByType(parameters, typeof(T));
                if (paramExpr != null)
                {
                    strb.Append(paramExpr.Name);
                    bTableFound = true;
                }
            }
            if (!bTableFound && parameters.Count > 0)
            {
                strb.Append(parameters[0].Name);
            }
            strb.Append(" SET ");
            BuildUpdateFields(strb, fields);
            strb.Append(" ");
            strb.Append(queryClause);

            return strb.ToString();
        }

        private void BuildUpdateFields<T, TFields>(StringBuilder strb, Expression<Func<T, TFields>> fields)
        {
            IEnumerable<ParameterExpression> parameters = fields.Parameters;
            Expression setExpr = fields.Body;
            if (setExpr.Type == typeof(T) && !(setExpr is NewExpression))
            {
                T t = (T)Expression.Lambda(setExpr).Compile().DynamicInvoke();
                foreach (var fieldmap in TypeMapper<T>.TableFieldMap.Values)
                {
                    strb.Append(_encoder.GetFieldName(fieldmap.Item1, null));
                    strb.Append("=");
                    //var value = fieldmap.Item2.GetValue(t, null);
                    var value = fieldmap.Item3.GetValue(t);
                    strb.Append(_encoder.GetConstant(value));
                    strb.Append(",");
                }

                strb.Length--;
            }
            else if ((setExpr is MemberExpression) &&
                ((MemberExpression)setExpr).Expression.Type == typeof(T))
            {
                MemberExpression memberExpr = setExpr as MemberExpression;

                string fieldName = TypeMapper<T>.GetQueryFieldName(memberExpr.Member.Name);
                if (fieldName != null)
                {
                    fieldName = _encoder.GetFieldName(fieldName, null);
                    strb.Append(fieldName);
                    strb.Append("=");
                    BuildFieldValue(strb, parameters, memberExpr);
                }

            }
            else
            {
                NewExpression newExpr = setExpr as NewExpression;
                if (newExpr != null)
                {
                    for (int i = 0; i < newExpr.Members.Count; i++)
                    {
                        string fieldName = TypeMapper<TFields>.GetQueryFieldName(newExpr.Members[i].Name);
                        if (fieldName != null)
                        {
                            fieldName = _encoder.GetFieldName(fieldName, null);
                            strb.Append(fieldName);
                            strb.Append("=");
                            BuildFieldValue(strb, parameters, newExpr.Arguments[i]);
                            strb.Append(",");
                        }
                    }
                    strb.Length--;
                }
                else
                {
                    throw new ArgumentException("更新字段列表为不支持的表达式类型", "fields");
                }
            }
            
        }

        protected object BuildFieldValue(StringBuilder strb, IEnumerable<ParameterExpression> parameters,
            Expression expression)
        {
            object val = null;

            bool foundWay = false;
            if (expression is UnaryExpression)
            {
                BuildFieldValueUnaryExpression(strb, parameters, (UnaryExpression)expression);
                foundWay = true;
            }
            else if (expression is BinaryExpression)
            {
                BuildBinaryExpression(strb, parameters, (BinaryExpression)expression, false);
                foundWay = true;
            }
            else if (expression is MemberExpression)
            {
                foundWay = BuildMemberExpression(strb, parameters, (MemberExpression)expression, false);
            }
            else if (expression is MethodCallExpression)
            {
                try
                {
                    BuildMethodCall(strb, parameters, (MethodCallExpression)expression);
                    foundWay = true;
                }
                catch
                {
                    // 如果找不到函数，则尝试计算函数的值
                }
            }
            else if (expression is ParameterExpression)
            {
                // 如果是参数，则取字段表
                BuildSelectFields(strb, parameters, (ParameterExpression)expression);
                foundWay = true;
            }
            else if (expression is ConstantExpression)
            {
                var constExpr = (ConstantExpression)expression;
                if (constExpr.Value is ISqlQueryable)
                {
                    BuildSelectFields(strb, (ISqlQueryable)constExpr.Value);
                    foundWay = true;
                }
            }

            if (!foundWay)
            {
                
                TryExtractValue(expression, ref val);
                strb.Append(_encoder.GetConstant(val));
            }
            return val;
        }

        public string BuildInsertCommand<T, TFields>(Expression<Func<T, TFields>> fields)
        {
            return BuildInsertCommand(fields, null);
        }

        public string BuildInsertCommand<T, TFields>(Expression<Func<T, TFields>> fields, string mappingTo)
        {
            if (fields == null)
            {
                throw new ArgumentNullException("fields", "插入字段列表不能为空");
            }

            StringBuilder strb = new StringBuilder();
            strb.Append("INSERT INTO ");
            strb.Append(GetTableName<T>(mappingTo));
            BuildInsertFields(strb, fields);

            return strb.ToString();
        }

        public string BuildInsertCommand<TFields>(IQueryable<TFields> query, string mappingTo)
        {
            Debug.Assert(!string.IsNullOrEmpty(mappingTo));

            StringBuilder strb = new StringBuilder();
            strb.Append("INSERT INTO ");
            strb.Append(mappingTo);
            strb.Append("(");
            foreach (var fieldmap in TypeMapper<TFields>.TableFieldMap.Values)
            {
                strb.Append(_encoder.GetFieldName(fieldmap.Item1, null));
                strb.Append(",");
            }
            strb.Length--;
            strb.Append(") ");
            strb.Append(BuildSelectCommand(query.Expression));

            return strb.ToString();
        }

        private void BuildInsertFields<T, TFields>(StringBuilder strb, Expression<Func<T, TFields>> fields)
        {
            IEnumerable<ParameterExpression> parameters = fields.Parameters;
            Expression setExpr = fields.Body;

            strb.Append("(");
            StringBuilder strValues = new StringBuilder();
            strValues.Append("VALUES(");

            MemberExpression memberExpr = setExpr as MemberExpression;
            if (memberExpr != null)
            {
                if (memberExpr.Type == typeof(T))
                {
                    T t = (T)MemberExpression.Lambda(memberExpr).Compile().DynamicInvoke();

                    foreach (var fieldmap in TypeMapper<T>.TableFieldMap.Values)
                    {
                        strb.Append(_encoder.GetFieldName(fieldmap.Item1, null));
                        strb.Append(",");

                        //var value = fieldmap.Item2.GetValue(t, null);
                        var value = fieldmap.Item3.GetValue(t);
                        strValues.Append(_encoder.GetFieldConstant<T>(fieldmap.Item2, value));
                        strValues.Append(",");
                    }
                    strb.Length--;
                    strValues.Length--;
                }
                else if (memberExpr.Expression.Type == typeof(T))
                {
                    string fieldName = TypeMapper<T>.GetQueryFieldName(memberExpr.Member.Name);
                    if (fieldName != null)
                    {
                        fieldName = _encoder.GetFieldName(fieldName, null);
                        strb.Append(fieldName);
                        BuildCondition(strValues, parameters, memberExpr, false);
                    }
                }
                else
                {
                    throw new ArgumentException("插入字段列表的类型不支持", "fields");
                }
            }
            else
            {
                NewExpression newExpr = setExpr as NewExpression;
                if (setExpr != null)
                {
                    for (int i = 0; i < newExpr.Members.Count; i++)
                    {
                        string fieldName = TypeMapper<TFields>.GetQueryFieldName(newExpr.Members[i].Name);
                        if (fieldName != null)
                        {
                            fieldName = _encoder.GetFieldName(fieldName, null);
                            strb.Append(fieldName);
                            strb.Append(",");
                            BuildCondition(strValues, parameters, newExpr.Arguments[i], false);
                            strValues.Append(",");
                        }
                    }
                    strb.Length--;
                    strValues.Length--;
                }
                else
                {
                    throw new ArgumentException("更新字段列表为不支持的表达式类型", "fields");
                }
            }

            strb.Append(") ");

            strValues.Append(")");
            strb.Append(strValues.ToString());
        }
    }
}
